/*
##############################################################################
# 
# Zope Public License (ZPL) Version 1.0
# -------------------------------------
# 
# Copyright (c) Digital Creations.  All rights reserved.
# 
# This license has been certified as Open Source(tm).
# 
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are
# met:
# 
# 1. Redistributions in source code must retain the above copyright
#    notice, this list of conditions, and the following disclaimer.
# 
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions, and the following disclaimer in
#    the documentation and/or other materials provided with the
#    distribution.
# 
# 3. Digital Creations requests that attribution be given to Zope
#    in any manner possible. Zope includes a "Powered by Zope"
#    button that is installed by default. While it is not a license
#    violation to remove this button, it is requested that the
#    attribution remain. A significant investment has been put
#    into Zope, and this effort will continue if the Zope community
#    continues to grow. This is one way to assure that growth.
# 
# 4. All advertising materials and documentation mentioning
#    features derived from or use of this software must display
#    the following acknowledgement:
# 
#      "This product includes software developed by Digital Creations
#      for use in the Z Object Publishing Environment
#      (http://www.zope.org/)."
# 
#    In the event that the product being advertised includes an
#    intact Zope distribution (with copyright and license included)
#    then this clause is waived.
# 
# 5. Names associated with Zope or Digital Creations must not be used to
#    endorse or promote products derived from this software without
#    prior written permission from Digital Creations.
# 
# 6. Modified redistributions of any form whatsoever must retain
#    the following acknowledgment:
# 
#      "This product includes software developed by Digital Creations
#      for use in the Z Object Publishing Environment
#      (http://www.zope.org/)."
# 
#    Intact (re-)distributions of any official Zope release do not
#    require an external acknowledgement.
# 
# 7. Modifications are encouraged but must be packaged separately as
#    patches to official Zope releases.  Distributions that do not
#    clearly separate the patches from the original work must be clearly
#    labeled as unofficial distributions.  Modifications which do not
#    carry the name Zope may be packaged in any form, as long as they
#    conform to all of the clauses above.
# 
# 
# Disclaimer
# 
#   THIS SOFTWARE IS PROVIDED BY DIGITAL CREATIONS ``AS IS'' AND ANY
#   EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
#   IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
#   PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL DIGITAL CREATIONS OR ITS
#   CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
#   SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
#   LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
#   USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
#   ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
#   OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
#   OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
#   SUCH DAMAGE.
# 
# 
# This software consists of contributions made by Digital Creations and
# many individuals on behalf of Digital Creations.  Specific
# attributions are listed in the accompanying credits file.
# 
##############################################################################

Set up schema and stored procedures for full storage implementation.

*/

create table zodb_pickle (
  z_oid         integer not null,  -- Object ID
  z_tid         integer not null,  -- Transaction ID
  z_pickle      long raw,          -- Pickle
  --
  constraint    zodb_pickle_pk primary key (z_oid, z_tid)
);

create table zodb_record (
  z_oid         integer not null,  -- Object ID
  z_tid         integer not null,  -- Transaction ID
  z_vid         integer not null,  -- Version ID
  z_pre         integer not null,  -- Previous transaction for oid
  z_nv          integer not null,  -- Non-version transaction 
                                   -- (not data) pointer
  z_data        integer not null,  -- Pickle data pointer
  --
  constraint    zodb_record_pk primary key (z_oid, z_tid)
);
-- need this for packing and undo, Waaaaa
create index zodb_record_tid_index on zodb_record (z_tid);
 
create table zodb_trans (
  z_tid         integer primary key,  -- Transaction ID
  z_status      char(1) not null,      -- Transaction status
  z_user        varchar(4000),         -- User info 
  z_desc        varchar(4000),         -- Description
  z_ext         varchar(4000)          -- Meta-data
);

create sequence zodb_version_sequence start with 1 increment by 1 nomaxvalue;

create table zodb_versions (
  z_vid integer primary key,
  z_version varchar(740)
);
create index zodb_version_index on zodb_versions (z_version);
insert into zodb_versions values (0, '');

create table zodb_current (
  z_oid         integer primary key,  -- Object ID
  z_tid         integer not null      -- Transaction ID
);

create table zodb_currentVersions (
  z_vid         integer not null,  -- Version ID
  z_oid         integer not null,  -- Object ID
  --
  constraint    zodb_currentVersion_pk primary key (z_vid, z_oid)
);

create table zodb_oids (
  z_oid         integer not null -- Object ID
);

create table zodb_packCurrent (
  z_oid         integer not null, -- Object ID
  z_tid         integer not null -- Transaction ID
);
create index zodb_packCurrent_index on zodb_packCurrent (z_oid);

commit;


create package zodb_methods as
  type ref_cursor is ref cursor;
  function zodb_abortVersion(i_tid in char, src in varchar,
                             oids out ref_cursor) return char;
  procedure zodb_begin(
      tid in char, status in char, 
      user in varchar, description in varchar, ext in varchar);
  function zodb_commitVersion(i_tid in char, src in varchar, dest in varchar, 
                              oids out ref_cursor) return char;
  procedure zodb_history(i_oid in char, history out ref_cursor);
  function zodb_len return integer;
  function zodb_size return integer;
  function zodb_load (i_oid in char, i_version in char, 
                      pickle out ref_cursor) return char;
  procedure zodb_load_serial (i_oid in char, i_serial in char,
                              pickle out ref_cursor);
  function zodb_max_oid return char;
  function zodb_max_tid return char;
  function zodb_store (
     i_oid in char, i_oldtid in char, i_version in varchar, i_tid in char
     , pickle in long raw
     ) return char;
  function zodb_undo(i_tid in char, oids out ref_cursor) return char;
  procedure zodb_undoLog(transactions out ref_cursor);
  function zodb_version (i_oid in char) return varchar;
  procedure zodb_versions (versions out ref_cursor);
  function zodb_versionEmpty (i_version in varchar) return integer;
  function zodb_vid(i_version in varchar) return integer;

  -- Packing:
  function zodb_packMark(i_tid in char) return integer;
  procedure zodb_packGetCurrent(i_tid in char);
  procedure zodb_packRemoveNonCurrent(i_tid in char);
  procedure zodb_packGC(i_tid in char);
  procedure zodb_packGCPickles(i_tid char);
  procedure zodb_packGCTrans(i_tid char);
  procedure zodb_packPickles(i_oid char, i_tid char,
                               pickles out ref_cursor);

end zodb_methods;
/

show errors;

----------------------------------------------------------------

create package body zodb_methods as

  ----------------------------------------------------------------
  function zodb_abortVersion(i_tid in char, src in varchar, 
                             oids out ref_cursor) return char 
  is
    vid integer;
    nvdata integer;
    cursor get_version is 
       select r.z_oid, r.z_tid, r.z_nv
       from zodb_currentVersions v, zodb_record r, zodb_current c
       where 
          v.z_vid = vid and                         -- Get version
          c.z_oid = v.z_oid and                     -- Get current
          r.z_oid = v.z_oid and r.z_tid = c.z_tid   -- Get record
          ;
  begin
      select z_vid into vid from zodb_versions where z_version = src;
      if vid is null then
         return null;
      end if;
      delete from zodb_oids;
      for src_record in get_version loop
         select z_data into nvdata from zodb_record
              where z_oid = src_record.z_oid and z_tid = src_record.z_nv;
         insert into zodb_record values (
           src_record.z_oid, i_tid, 0, src_record.z_tid, 0, nvdata
         );
         insert into zodb_oids values (src_record.z_oid);
         update zodb_current set z_tid = i_tid where z_oid = src_record.z_oid;
      end loop;
      delete from zodb_currentVersions where z_vid = vid;
      open oids for select to_char(z_oid) from zodb_oids;
      return null;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  procedure zodb_begin(
    tid in char, status in char, 
    user in varchar, description in varchar, ext in varchar) 
  is
  begin
     insert into zodb_trans values (tid, status, user, description, ext);
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_commitVersion(i_tid in char, src in varchar, dest in varchar,
                              oids out ref_cursor) return char 
  is
    vid integer;
    dvid integer;
    cursor get_version is 
       select r.z_oid, r.z_tid, r.z_nv, r.z_data
       from zodb_currentVersions v, zodb_record r, zodb_current c
       where 
          v.z_vid = vid and                         -- Get version
          c.z_oid = v.z_oid and                     -- Get current
          r.z_oid = v.z_oid and r.z_tid = c.z_tid   -- Get record
          ;
  begin
      select z_vid into vid from zodb_versions where z_version = src;
      if vid is null or dest = src then
         return null;
      end if;
      if dest is not null then
          dvid := zodb_vid(dest);
      else
          dvid := 0;
      end if;
      delete from zodb_oids;
      for src_record in get_version loop
         insert into zodb_record values (
           src_record.z_oid, i_tid, dvid, src_record.z_tid, 
           src_record.z_nv, src_record.z_data
         );
         insert into zodb_oids values (src_record.z_oid);
         update zodb_current set z_tid = i_tid where z_oid = src_record.z_oid;
         if dvid != 0 then
           insert into zodb_currentVersions values (dvid, dest);
         end if;
      end loop;
      delete from zodb_currentVersions where z_vid = vid;
      open oids for select to_char(z_oid) from zodb_oids;
      return null;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  procedure zodb_history(i_oid in char, history out ref_cursor) 
  is
  begin
     open history for 
        select to_char(t.z_tid), z_user, z_desc, z_ext, v.z_version
        from zodb_record r, zodb_trans t, zodb_pickle p, zodb_versions v
        where r.z_oid = i_oid and t.z_tid = r.z_tid and
              p.z_oid = i_oid and p.z_tid = r.z_tid and
              v.z_vid = r.z_vid
        order by t.z_tid desc;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_len return integer 
  is
     n integer;
  begin
     select count(z_oid) into n from zodb_current;
     return n;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_size return integer 
  is
     n integer;
  begin
     select count(*) into n from zodb_record;
     return n;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_load(i_oid in char, i_version in char, 
                     pickle out ref_cursor) return char 
  is
    v varchar(750);
    tid integer;
    nv integer;
    data integer;
    cursor c is
      select r.z_tid, v.z_version, r.z_nv, r.z_data 
      from zodb_record r, zodb_current c, zodb_versions v
      where c.z_oid = i_oid                               -- requested oid
            and r.z_oid = c.z_oid and r.z_tid = c.z_tid   -- link to record
            and v.z_vid = r.z_vid;                         -- link to version
  begin 
    for cur in c loop
       if cur.z_version is not null and cur.z_version != i_version then
           open pickle for 
               select z_pickle 
               from zodb_pickle p, zodb_record r
               where 
                   r.z_oid = i_oid and r.z_tid = cur.z_nv -- nv record
                   and
                   p.z_oid = i_oid and p.z_tid = r.z_data;    -- data
           return cur.z_nv;
       else
           open pickle for 
               select z_pickle from zodb_pickle
               where z_oid = i_oid and z_tid = cur.z_data;
           return cur.z_tid;
       end if;
    end loop;
    open pickle for 
       select z_pickle from zodb_pickle where 1=2; -- Return nothing!
    return null;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  procedure zodb_load_serial (i_oid in char, i_serial in char,
                              pickle out ref_cursor) 
  is
  begin 
    open pickle for select z_pickle from zodb_pickle
         where z_oid = i_oid and z_tid = i_serial;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_max_oid return char 
  is
     c integer;
  begin
     select max(z_oid) into c from zodb_current;
     return c;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_max_tid return char 
  is
     c integer;
  begin
     select max(z_tid) into c from zodb_trans;
     return c;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_store (
    i_oid in char, i_oldtid in char, i_version in varchar, i_tid in char,
    pickle in long raw
    ) return char 
  is
    tid integer;
    nv integer;
    vid integer;
    pre integer;
    v varchar(750);
    cursor c_store is select c.z_tid, z_pre, z_version, z_nv, r.z_vid 
      from zodb_record r, zodb_versions v, zodb_current c
      where
        c.z_oid = i_oid                              -- current tid
        and r.z_oid = i_oid and r.z_tid = c.z_tid    -- record
        and v.z_vid = r.z_vid;                       -- version name
  begin
    open c_store;
    fetch c_store into tid, pre, v, nv, vid;
    if c_store%FOUND then
        -- There's an old record

        -- Check for pathalogical case of second record for oid in trans
        if i_tid = tid then
            -- Duplicate data. This one wins, so delete old
            delete from zodb_record where z_oid = i_oid and z_tid = i_tid;
            delete from zodb_pickle where z_oid = i_oid and z_tid = i_tid;
            if pre = 0 then
                delete from zodb_current where z_oid = i_oid;
            else
                update zodb_current set z_tid = pre where z_oid = i_oid;
            end if;
            close c_store;
            return zodb_store (i_oid, pre, i_version, i_tid, pickle);
        end if;

        -- Check for conflict
        if tid != i_oldtid then
            close c_store;
            return 'c' || tid; -- Conflict error
        end if;

        if v is not null then
           -- We're in a version
           if v != i_version then
               close c_store;
               return 'v' || v; -- Version lock error
           end if;
           -- We are continuing work in the same version
        else
           if i_version is not null then
               -- We're starting to work in a version
               nv := tid;
               vid := zodb_vid(i_version);
               insert into zodb_currentVersions values (vid, i_oid);
           else
               nv := 0;
           end if;
        end if;

        update zodb_current set z_tid = i_tid where z_oid = i_oid;
    else
       tid := 0;
       nv := 0;
       if i_version is not null then
           -- We're starting to work in a version
           vid := zodb_vid(i_version);
           insert into zodb_currentVersions values (vid, i_oid);
       else
           vid := 0;
       end if;
       insert into zodb_current values (i_oid, i_tid);
    end if;
    close c_store;
    insert into zodb_record values (i_oid, i_tid, vid, tid, nv, i_tid);
    if pickle is not null then
        insert into zodb_pickle values (i_oid, i_tid, pickle);
    end if;
    return null;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_undo(i_tid in char, oids out ref_cursor) return char
  is
    cursor transaction_records is 
      select r.z_tid, r.z_oid, r.z_pre, c.z_tid as z_ctid, r.z_vid
      from zodb_record r, zodb_current c
      where r.z_tid = i_tid      -- Get records for transaction
        and c.z_oid = r.z_oid    -- Get the current trans for id
        ;
    vid integer;
  begin
    for record in transaction_records loop
        if record.z_tid != record.z_ctid then
            return record.z_oid;
        end if;
        if record.z_pre = 0 then
            delete from zodb_current where z_oid = record.z_oid;
            vid := 0;
        else
            update zodb_current 
               set z_tid = record.z_pre where z_oid = record.z_oid;
            select z_vid into vid 
               from zodb_record 
               where z_oid = record.z_oid and z_tid = record.z_pre;
        end if;
        if vid != record.z_vid then
           -- We are undoing a record that changed versions
           if record.z_vid != 0 then
               delete from zodb_currentVersions 
                  where z_vid = record.z_vid and z_oid = record.z_oid;
           end if;
           if vid != 0 then
               insert into zodb_currentVersions values (vid, record.z_oid);
           end if;
        end if;
        insert into zodb_oids values (record.z_oid);
    end loop;
    delete from zodb_trans where z_tid = i_tid;
    delete from zodb_record where z_tid = i_tid;
    open oids for select to_char(z_oid) from zodb_oids;
    return null;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  procedure zodb_undoLog(transactions out ref_cursor)
  is
  begin
     open transactions for 
        select to_char(z_tid), z_user, z_desc, z_ext
        from zodb_trans where z_status = ' '
        order by z_tid desc;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_version (i_oid in char) return varchar 
  is
     o_version varchar(750);
     cursor c is select z_version 
     from zodb_currentVersions cv, zodb_versions v
     where z_oid = i_oid and v.z_vid = cv.z_vid;
  begin
     open c;
     fetch c into o_version;
     close c;
     return o_version;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  procedure zodb_versions (versions out ref_cursor)
  is
  begin
     open versions for select unique z_version 
     from zodb_currentVersions cv, zodb_versions v
     where cv.z_vid = v.z_vid;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_versionEmpty (i_version in varchar) return integer 
  is
    cursor c_versionEmpty is select cv.z_vid
      from zodb_currentVersions cv, zodb_versions v
      where z_version = i_version and cv.z_vid = v.z_vid;
  begin
    for r in c_versionEmpty loop
      return 0;
    end loop;
    return 1;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  function zodb_vid(i_version in varchar) return integer 
  is 
      vid integer;
      cursor c is
        select z_vid from zodb_versions where z_version = i_version;
  begin
      open c;
      fetch c into vid;
      close c;
      if vid is null then
          select zodb_version_sequence.nextval into vid from dual;
          insert into zodb_versions values (vid, i_version);
      end if;
      return vid;
  end;   
  ----------------------------------------------------------------
  

  ----------------------------------------------------------------
  ---
  --- PACKING!
  ---
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  -- Begin packaing by marking transaction records
  -- and making sure we have something to pack.
  function zodb_packMark(i_tid in char) return integer
  is 
  begin
     update zodb_trans set z_status = 'p' 
            where z_tid < i_tid and z_status = ' ';
     -- Make sure we have something to do. This is not just
     -- an optimization, packing already packed data can do harm!
     if SQL%ROWCOUNT < 1 then
         return 0;
     end if;
     return 1;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  -- Figure out the current records as of the pack time. 
  -- Later, we will remove the reachable records 
  procedure zodb_packGetCurrent(i_tid in char)
  is
     cursor cur is
       select z_oid, max(z_tid) as z_tid
       from zodb_record
       where z_tid < i_tid
       group by z_oid;
     nv integer;
  begin
      -- Get the current records *as of* the pack time
      delete from zodb_packCurrent;
      for current_record in cur loop
         insert into zodb_packCurrent values (
           current_record.z_oid, 
           current_record.z_tid);
         select z_nv into nv from zodb_record 
            where z_oid = current_record.z_oid and
                  z_tid = current_record.z_tid;
         if nv is not null and nv != 0 then
             insert into zodb_packCurrent values (
                 current_record.z_oid, nv);
         end if;
      end loop;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  -- Remove the non-current data 
  procedure zodb_packRemoveNonCurrent(i_tid in char)
  is
  begin
      -- Delete records from before pack time that were not current
      delete from zodb_record r
      where
          z_tid < i_tid and
          z_tid not in (select z_tid from zodb_packCurrent
                        where z_oid = r.z_oid);
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  -- GC unreachable records as of the pack time */
  procedure zodb_packGC(i_tid in char)
  is
      cursor cur is select z_oid from zodb_packCurrent;
  begin
      for r in cur loop
          delete from zodb_record          where z_oid = r.z_oid;
          delete from zodb_current         where z_oid = r.z_oid;
          delete from zodb_currentVersions where z_oid = r.z_oid;
          -- The following could be done by zodb_packGCPickles (below)
          -- but we have the benefit of a more direct index here
          -- and we can leave less work for zodb_packGCPickles to do.
          delete from zodb_pickle          where z_oid = r.z_oid;
      end loop;
      delete from zodb_packCurrent;
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  -- GC no-longer referenced pickle records 
  -- Note that this includes pickle records for
  -- objects ids that are still valid but for records
  -- that have been packed away.
  procedure zodb_packGCPickles(i_tid char) 
  is
  begin
      delete from zodb_pickle p
      where z_tid < i_tid and
         z_tid not in (select z_tid from zodb_record where z_oid = p.z_oid);
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  -- GC no-longer referenced transaction records 
  procedure zodb_packGCTrans(i_tid char) 
  is
  begin
      delete from zodb_trans
      where z_tid < i_tid and
         z_tid not in (select z_tid from zodb_record);
  end;
  ----------------------------------------------------------------

  ----------------------------------------------------------------
  -- Find the pickles for records matching i_oid before the pack time */
  procedure zodb_packPickles(i_oid char, i_tid char,
                               pickles out ref_cursor) 
  is
  begin
     delete from zodb_packCurrent where z_oid = i_oid;
     open pickles for select p.z_pickle 
        from zodb_pickle p, zodb_record r
        where r.z_oid = i_oid and r.z_tid < i_tid and
              p.z_oid = r.z_oid and
              p.z_tid = r.z_tid;
  end;  
  ----------------------------------------------------------------


end zodb_methods;
/

show errors;

quit;
